USE master;
GO

SET NOCOUNT ON;
GO

-- potential parallel plans
SELECT
	qs.total_worker_time,
    qs.total_elapsed_time,
    SUBSTRING(qt.text, qs.statement_start_offset / 2,
        (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) AS query_text,
    qs.execution_count,
    qs.sql_handle,
    qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qs.total_worker_time > qs.total_elapsed_time
ORDER BY qs.total_worker_time DESC;

-- check the query plan
/*
SELECT
	query_plan
FROM sys.dm_exec_query_plan (<plan_handle>);
*/

-- estimated cost of parallel execution plans
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
	query_plan,
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS statement_text,
    n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS statement_optimization_level,
    n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS statement_subtree_cost,
    n.query('.') AS parallel_subtree_xml,
    ecp.usecounts,
    ecp.size_in_bytes
FROM sys.dm_exec_cached_plans ecp
     CROSS APPLY sys.dm_exec_query_plan(plan_handle) eqp
     CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
     AS qn ( n )
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
OPTION (MAXDOP 1);